package model;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import javax.naming.NamingException;
import javax.sql.rowset.CachedRowSet;

import com.sun.rowset.CachedRowSetImpl;

import db.DbConnectionPool;
import db.DbException;

public class ApplicantFetchModel {
	
	private static String fetchByReviewer = "";
	private static String fetchByDiscipline ="SELECT firstname,middle,lastname,Students.id "+
                               "FROM  Students,Degrees "+
                               "WHERE Degrees.discipline = ? "+
                               "AND Degrees.students = Students.id";   
	
	private static String fetchBySpecialization = "SELECT firstname,middle,lastname,id "+
	                               "FROM  Students "+
	                               "WHERE Students.specialization = ?"; 
	
	public static CachedRowSet fetchByReviewer() throws DbException{
		try{
			Connection conn = DbConnectionPool.getConnection();
			Statement stmt = conn.createStatement();
			ResultSet rs = stmt.executeQuery(fetchByReviewer);
		
			CachedRowSet crsApplicant = new CachedRowSetImpl();
			crsApplicant.populate(rs);
			
        rs.close();
        stmt.close();
        conn.close();
        return crsApplicant;
        
		} catch (SQLException ex) {
            throw new DbException(ex);
		} catch (NamingException ex) {
			throw new DbException(ex);
		}
	}
	
	
	public static CachedRowSet fetchByDiscipline(int DiscApp) throws DbException{
		try{
			Connection conn = DbConnectionPool.getConnection();
			PreparedStatement pstmt = conn.prepareStatement(fetchByDiscipline);
			pstmt.setInt(1,DiscApp);
			ResultSet rs = pstmt.executeQuery();
		
			CachedRowSet crsApplicant = new CachedRowSetImpl();
			crsApplicant.populate(rs);
			
        rs.close();
        pstmt.close();
        conn.close();
        return crsApplicant;
        
		} catch (SQLException ex) {
            throw new DbException(ex);
		} catch (NamingException ex) {
			throw new DbException(ex);
		}
	}
	
	
	
	public static CachedRowSet fetchBySpecialization(int SpecApp) throws DbException{
		try{

			Connection conn = DbConnectionPool.getConnection();
			PreparedStatement pstmt = conn.prepareStatement(fetchBySpecialization);
			pstmt.setInt(1,SpecApp);
			ResultSet rs = pstmt.executeQuery();
		
			CachedRowSet crsApplicant = new CachedRowSetImpl();
			crsApplicant.populate(rs);
			
        rs.close();
        pstmt.close();
        conn.close();
        return crsApplicant;
        
		} catch (SQLException ex) {
            throw new DbException(ex);
		} catch (NamingException ex) {
			throw new DbException(ex);
		}
	}
	
	

}
